﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Utility.Helper;

namespace BLL
{
    public class QS_PLAN_DETAIL3_BLL : BaseBLL
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name="proId">项目id</param>
        /// <param name="N_PROTYPE">1工程、大修项目;2其他大额支出;3其他产品及服务4投资项目</param>
        /// <param name="type">类型：1 年资金,2 月资金,3 周资金</param>
        /// <returns></returns>
        public List<QS_PLAN_DETAIL3> FindRange3(int applyId, int proId, int N_PROTYPE, int type, string userId = "null")
        {
            if (userId == "null")
            {
                userId = base.User.USER_ID.ToString();
            }

            string sql = null;

            //查询周(汇总或成员)
            if (type == 3 || type == 5)
            {
                sql = @"
  SELECT
    T2.*,
    CASE
      WHEN T2.N_PLANAMOUNT IS NULL THEN '0%'
      WHEN T2.N_PLANAMOUNT =0 THEN '0%'
      WHEN T2.N_REAMOUNT IS NULL THEN '0%'
      WHEN T2.N_REAMOUNT =0 THEN '0%'
      ELSE
         TO_CHAR(ROUND(T2.N_REAMOUNT/T2.N_PLANAMOUNT*100,6))||'%'
    END AS N_GOL
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL3 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE AND T2.N_PRO_ID = :N_PRO_ID AND T2.N_PROTYPE = :N_PROTYPE
  WHERE
        T1.N_WEEKAPP_ID = :APP_ID";

                //若为周成员则添加一个条件
                if (type == 5)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }
            }

            //查询月(汇总或成员)
            if (type == 2 || type == 4)
            {
                sql = @"SELECT
  T1.*,
  T2.N_GOL
FROM
  (SELECT
    T2.*
  FROM QS_PLANMONTHAPPLY T1
  INNER JOIN QS_PLAN_DETAIL3 T2 ON T1.N_MONTHAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE AND T2.N_PRO_ID = :N_PRO_ID AND T2.N_PROTYPE = :N_PROTYPE";

                //若为月成员则添加一个条件
                if (type == 4)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }

                sql += @" WHERE
    T1.N_MONTHAPP_ID = :APP_ID
  ) T1
FULL JOIN (
SELECT
  T1.*,
  CASE
      WHEN T1.N_PLANAMOUNT IS NULL THEN '0%'
      WHEN T1.N_PLANAMOUNT = 0 THEN '0%'
      WHEN T1.N_REAMOUNT IS NULL THEN '0%'
      WHEN T1.N_REAMOUNT = 0 THEN '0%'
      ELSE TO_CHAR(ROUND(T1.N_REAMOUNT/T1.N_PLANAMOUNT*100,2))||'%'
   END AS N_GOL
FROM
(
  SELECT
    T2.N_CUSTOMER_ID,
    T2.C_PRONAME,
		SUM(
      CASE
        WHEN T2.N_PLANAMOUNT IS NULL THEN 0
        ELSE T2.N_PLANAMOUNT
      END) AS N_PLANAMOUNT,
		SUM(
      CASE
        WHEN T2.N_REAMOUNT IS NULL THEN 0
        ELSE T2.N_REAMOUNT
      END) AS N_REAMOUNT
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL3 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
  INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
  WHERE
    T1.N_BUSINESS_STATE = 2 AND
    T1.N_MONTHAPP_ID = :APP_ID AND
    T2.N_PRO_ID = :N_PRO_ID AND
    T2.N_PROTYPE = :N_PROTYPE
  GROUP BY
    T2.N_CUSTOMER_ID,T2.C_PRONAME) T1) T2 ON T1.N_CUSTOMER_ID = T2.N_CUSTOMER_ID AND T1.C_PRONAME = T2.C_PRONAME";
            }

            List<Tuple<string, string, string>> parms = new List<Tuple<string, string, string>>();

            if (type == 4 || type == 5)
            {
                parms.Add(new Tuple<string, string, string>(":N_USERID", userId, "int"));
            }

            parms.Add(new Tuple<string, string, string>(":APP_ID", applyId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_TYPE", type.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_PRO_ID", proId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_PROTYPE", N_PROTYPE.ToString(), "int"));

            var dt = Query(sql, parms);

            var entitys = new List<QS_PLAN_DETAIL3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAIL3();
                entity.N_ID3 = decimal.Parse(row["N_ID3"].ToString());
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = decimal.Parse(row["N_TYPE"].ToString());

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = row["N_NUM"] == null ? 0 : decimal.Parse(row["N_NUM"].ToString());
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == null ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_AMOUT_TYPE = row["C_AMOUT_TYPE"].ToString();
                entity.C_AMOUT_USE = row["C_AMOUT_USE"].ToString();
                entity.N_GZAMOUNT = row["N_GZAMOUNT"] == null ? 0 : decimal.Parse(row["N_GZAMOUNT"].ToString());
                entity.N_ZBAMOUNT = row["N_ZBAMOUNT"] == null ? 0 : decimal.Parse(row["N_ZBAMOUNT"].ToString());

                entity.N_PLANAMOUNT = row["N_PLANAMOUNT"] == null ? 0 : decimal.Parse(row["N_PLANAMOUNT"].ToString());
                entity.N_SPAMOUNT1 = row["N_SPAMOUNT1"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT1"].ToString());

                entity.N_SPAMOUNT2 = row["N_SPAMOUNT2"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT2"].ToString());
                entity.N_REAMOUNT = row["N_REAMOUNT"] == null ? 0 : decimal.Parse(row["N_REAMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entity.N_MONTHJEUSE = row["N_MONTHJEUSE"] == null ? 0 : decimal.Parse(row["N_MONTHJEUSE"].ToString());
                entity.N_PROTYPE = row["N_PROTYPE"] == null ? 0 : decimal.Parse(row["N_PROTYPE"].ToString());
                entity.C_PRONAME = row["C_PRONAME"].ToString();

                //entity.N_AMT = row["N_USEAMOUNT"] == null ? 0 : decimal.Parse(row["N_USEAMOUNT"].ToString());
                entity.C_AMTPRO = row["N_GOL"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(月)
        /// </summary>
        public void GetMonthCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       N_CUSTOMER_ID,
       C_CUSTOMER_NAME,
       C_AMOUT_TYPE,
       C_AMOUT_USE,
       round(sum(N_GZAMOUNT),2) N_GZAMOUNT,
       round(sum(N_ZBAMOUNT),2) N_ZBAMOUNT,
       round(sum(N_PLANAMOUNT),2) N_PLANAMOUNT,
       round(sum(N_SPAMOUNT1),2) N_SPAMOUNT1,
       round(sum(N_SPAMOUNT2),2) N_SPAMOUNT2,
       round(sum(N_REAMOUNT),2) N_REAMOUNT,
       round(sum(N_MONTHJEUSE),2) N_MONTHJEUSE,
       N_PROTYPE,
       C_PRONAME,
       round(sum(N_LASTMONTHJEUSE),2) N_LASTMONTHJEUSE
  from qs_plan_detail3 t
 WHERE t.n_type = 4
   and t.n_pro_id = " + proId + @"
   and t.n_planapp_id = " + applyId + @"
 GROUP BY N_PRO_ID,
          N_TYPE,
          N_PLANAPP_ID,
          N_CUSTOMER_ID,
          C_CUSTOMER_NAME,
          C_AMOUT_TYPE,
          C_AMOUT_USE,
          N_PROTYPE,
          C_PRONAME";

            var dt = Query(sql);
            decimal num = 0;
            var entitys = new List<QS_PLAN_DETAIL3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;
                var entity = new QS_PLAN_DETAIL3();
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 2;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = num;
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == null ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_AMOUT_TYPE = row["C_AMOUT_TYPE"].ToString();
                entity.C_AMOUT_USE = row["C_AMOUT_USE"].ToString();
                entity.N_GZAMOUNT = row["N_GZAMOUNT"] == null ? 0 : decimal.Parse(row["N_GZAMOUNT"].ToString());
                entity.N_ZBAMOUNT = row["N_ZBAMOUNT"] == null ? 0 : decimal.Parse(row["N_ZBAMOUNT"].ToString());

                entity.N_PLANAMOUNT = row["N_PLANAMOUNT"] == null ? 0 : decimal.Parse(row["N_PLANAMOUNT"].ToString());
                entity.N_SPAMOUNT1 = row["N_SPAMOUNT1"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT1"].ToString());

                entity.N_SPAMOUNT2 = row["N_SPAMOUNT2"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT2"].ToString());
                entity.N_REAMOUNT = row["N_REAMOUNT"] == null ? 0 : decimal.Parse(row["N_REAMOUNT"].ToString());
                entity.C_BANK = "";

                entity.C_REMARK = "";

                entity.N_MONTHJEUSE = row["N_MONTHJEUSE"] == null ? 0 : decimal.Parse(row["N_MONTHJEUSE"].ToString());
                entity.N_PROTYPE = row["N_PROTYPE"] == null ? 0 : decimal.Parse(row["N_PROTYPE"].ToString());
                entity.C_PRONAME = row["C_PRONAME"].ToString();
                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                if (entitys.Where(e => e.N_PROTYPE == 1).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 1, 2, entitys);
                }

                if (entitys.Where(e => e.N_PROTYPE == 2).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 2, 2, entitys);
                }
                if (entitys.Where(e => e.N_PROTYPE == 3).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 3, 2, entitys);
                }
                if (entitys.Where(e => e.N_PROTYPE == 4).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 4, 2, entitys);
                }
            }
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(周)
        /// </summary>
        public void GetWeekCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       N_CUSTOMER_ID,
       C_CUSTOMER_NAME,
       C_AMOUT_TYPE,
       C_AMOUT_USE,
       C_BANK,
       C_REMARK,
       round(sum(N_GZAMOUNT),6) N_GZAMOUNT,
       round(sum(N_ZBAMOUNT),6) N_ZBAMOUNT,
       round(sum(N_PLANAMOUNT),6) N_PLANAMOUNT,
       round(sum(N_SPAMOUNT1),6) N_SPAMOUNT1,
       round(sum(N_SPAMOUNT2),6) N_SPAMOUNT2,
       round(sum(N_REAMOUNT),6) N_REAMOUNT,
       round(sum(N_MONTHJEUSE),6) N_MONTHJEUSE,
       N_PROTYPE,
       C_PRONAME,
       round(sum(N_LASTMONTHJEUSE),6) N_LASTMONTHJEUSE
  from qs_plan_detail3 t
 WHERE t.n_type = 5
   and t.n_pro_id = " + proId + @"
   and t.n_planapp_id = " + applyId + @"
 GROUP BY N_PRO_ID,
          N_TYPE,
          N_PLANAPP_ID,
          N_CUSTOMER_ID,
          C_CUSTOMER_NAME,
          C_AMOUT_TYPE,
          C_AMOUT_USE,
          N_PROTYPE,
          C_PRONAME,
          C_BANK,
          C_REMARK";

            var dt = Query(sql);
            decimal num = 0;
            var entitys = new List<QS_PLAN_DETAIL3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;
                var entity = new QS_PLAN_DETAIL3();
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 3;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = num;
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == null ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_AMOUT_TYPE = row["C_AMOUT_TYPE"].ToString();
                entity.C_AMOUT_USE = row["C_AMOUT_USE"].ToString();
                entity.N_GZAMOUNT = row["N_GZAMOUNT"] == null ? 0 : decimal.Parse(row["N_GZAMOUNT"].ToString());
                entity.N_ZBAMOUNT = row["N_ZBAMOUNT"] == null ? 0 : decimal.Parse(row["N_ZBAMOUNT"].ToString());

                entity.N_PLANAMOUNT = row["N_PLANAMOUNT"] == null ? 0 : decimal.Parse(row["N_PLANAMOUNT"].ToString());
                entity.N_SPAMOUNT1 = row["N_SPAMOUNT1"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT1"].ToString());

                entity.N_SPAMOUNT2 = row["N_SPAMOUNT2"] == null ? 0 : decimal.Parse(row["N_SPAMOUNT2"].ToString());
                entity.N_REAMOUNT = row["N_REAMOUNT"] == null ? 0 : decimal.Parse(row["N_REAMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entity.N_MONTHJEUSE = row["N_MONTHJEUSE"] == null ? 0 : decimal.Parse(row["N_MONTHJEUSE"].ToString());
                entity.N_PROTYPE = row["N_PROTYPE"] == null ? 0 : decimal.Parse(row["N_PROTYPE"].ToString());
                entity.C_PRONAME = row["C_PRONAME"].ToString();
                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                if (entitys.Where(e => e.N_PROTYPE == 1).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 1, 3, entitys);
                }

                if (entitys.Where(e => e.N_PROTYPE == 2).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 2, 3, entitys);
                }
                if (entitys.Where(e => e.N_PROTYPE == 3).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 3, 3, entitys);
                }
                if (entitys.Where(e => e.N_PROTYPE == 4).Count() > 0)
                {
                    UpdateOrCreateRange3(applyId, proId, 4, 3, entitys);
                }
            }
        }

        /// <summary>
        /// 查询周银行流水
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name=""></param>
        public DataTable GetBANKDETAILLIST(int applyId, int proId, int N_PROTYPE, int type, string userid = "null")
        {
            StringBuilder stb = new StringBuilder();

            stb.Append(@"SELECT  T1.*,
ROUND((CASE WHEN T4.N_AMT IS NULL THEN 0 ELSE T4.N_AMT END),2) AS N_AMT,
(CASE WHEN (CASE WHEN T1.N_PRICE IS NULL THEN 0 ELSE T1.N_PRICE END) = 0 THEN '0%'
WHEN (CASE WHEN T4.N_AMT IS NULL THEN 0 ELSE T4.N_AMT END) = 0 THEN '0%'
ELSE TO_CHAR(ROUND(T1.N_PRICE/T4.N_AMT*100,2))||'%' END) AS C_AMTPRO FROM QS_PLAN_DETAIL2 T1
INNER JOIN QS_PLANWEEK_DETAIL T2
ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T1.N_PRO_ID = T2.N_PROID AND T1.N_PRO_ID = :N_PRO_ID AND T1.N_TYPE = :N_TYPE AND T1.N_PROTYPE= :N_PROTYPE");

            //若是周详情则根据周appid查询
            if (type == 3 || type == 5)
            {
                stb.Append(@" AND T1.N_PLANAPP_ID = :N_PLANAPP_ID ");
            }

            //若是月详情则根据月appid查询所有周appid
            if (type == 2 || type == 4)
            {
                stb.Append(@" AND T1.N_PLANAPP_ID in (SELECT N_WEEKAPP_ID FROM QS_PLANWEEKAPPLY WHERE N_MONTHAPP_ID = :N_PLANAPP_ID) ");
            }

            //若传入了用户id 查询type:4,5的数据
            if (userid != "null")
            {
                stb.Append(@" AND N_USERID = :N_USERID ");
            }

            stb.Append(@" LEFT JOIN QS_PLANWEEKAPPLY T3
ON T2.N_WEEKAPP_ID = T3.N_WEEKAPP_ID AND T3.N_BUSINESS_STATE = 1
LEFT JOIN (SELECT D.N_WEEKDETAIL_ID,C.N_ID,SUM(D.N_AMT) AS N_AMT FROM YX_BANK_DETAILLIST D LEFT JOIN YX_CUSTOMER C ON D.C_ACC_NAME1 = C.C_NAME GROUP BY C.N_ID,D.N_WEEKDETAIL_ID) T4
 ON T2.N_WEEKDETAIL_ID = T4.N_WEEKDETAIL_ID AND
T1.N_CUSTOMER_ID = T4.N_ID
ORDER BY T1.N_ID2");

            List<Tuple<string, string, string>> parms = new List<Tuple<string, string, string>>();
            parms.Add(new Tuple<string, string, string>(":N_PLANAPP_ID", applyId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_PRO_ID", proId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_TYPE", type.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_PROTYPE", N_PROTYPE.ToString(), "int"));

            if (userid != "null")
            {
                parms.Add(new Tuple<string, string, string>(":N_USERID", userid.ToString(), "int"));
            }

            return Query(stb.ToString(), parms);
        }

        public bool DeleteByApplyId(decimal applyId, decimal proId, int N_TYPE, int? code = null, string userid = null)
        {
            var sql = "DELETE qs_plan_detail3 WHERE N_PLANAPP_ID=" + applyId + " AND N_PRO_ID=" + proId + @" AND N_TYPE=" + N_TYPE;
            if (code != null)
            {
                sql += " AND N_PROTYPE='" + code + "'";
            }

            if (!string.IsNullOrEmpty(userid))
            {
                sql += " AND N_USERID = " + userid;
            }

            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert(QS_PLAN_DETAIL3 entity, string userid = "NULL")
        {
            var seq = GetSequence("SEQ_N_ID3");
            entity.N_ID3 = seq;
            var sql = @"insert into qs_plan_detail3
  (n_id3,
   n_pro_id,
   n_type,
   n_planapp_id,
   n_num,
   n_customer_id,
   c_customer_name,
   c_amout_type,
   c_amout_use,
   n_gzamount,
   n_zbamount,
   n_planamount,
   n_spamount1,
   n_spamount2,
   n_reamount,
   c_bank,
   c_remark,
   n_monthjeuse,
   n_protype,
   c_proname,
    n_userid)
values
  (" + entity.N_ID3 + @",
   " + entity.N_PRO_ID + @",
   " + entity.N_TYPE + @",
     " + entity.N_PLANAPP_ID + @",
  " + entity.N_NUM + @",
  " + entity.N_CUSTOMER_ID + @",
    '" + entity.C_CUSTOMER_NAME + @"',
    '" + entity.C_AMOUT_TYPE + @"',
    '" + entity.C_AMOUT_USE + @"',
  " + entity.N_GZAMOUNT + @",
  " + entity.N_ZBAMOUNT + @",
  " + entity.N_PLANAMOUNT + @",
  " + entity.N_SPAMOUNT1 + @",
  " + entity.N_SPAMOUNT2 + @",
  " + entity.N_REAMOUNT + @",
  '" + entity.C_BANK + @"',
  '" + entity.C_REMARK + @"',
   0,
  " + entity.N_PROTYPE + @",
     '" + entity.C_PRONAME + @"',
    " + userid + @")";

            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId">申请id</param>
        /// <param name="proId">项目id</param>
        /// <param name="N_PROTYPE">1工程、大修项目;2其他大额支出;3其他产品及服务4投资项目</param>
        /// <param name="type">类型：1 年资金,2 月资金,3 周资金</param>
        /// <param name="data"></param>
        public void UpdateOrCreateRange3(int applyId, int proId, int N_PROTYPE, int type, List<QS_PLAN_DETAIL3> data)
        {
            //先清空再添加
            if (type == 4 || type == 5)
            {
                DeleteByApplyId(applyId, proId, type, N_PROTYPE, base.User.USER_ID.ToString());
            }
            else
            {
                DeleteByApplyId(applyId, proId, type, N_PROTYPE);
            }

            int count = 0;
            foreach (var item in data.Where(d => d.N_PROTYPE == N_PROTYPE))
            {
                var entity = new QS_PLAN_DETAIL3();
                entity.N_PRO_ID = item.N_PRO_ID;
                entity.N_TYPE = item.N_TYPE;
                entity.N_PLANAPP_ID = item.N_PLANAPP_ID;
                entity.N_NUM = item.N_NUM;
                entity.N_CUSTOMER_ID = item.N_CUSTOMER_ID;
                entity.C_CUSTOMER_NAME = item.C_CUSTOMER_NAME;
                entity.C_AMOUT_TYPE = item.C_AMOUT_TYPE;
                entity.C_AMOUT_USE = item.C_AMOUT_USE;
                entity.N_GZAMOUNT = item.N_GZAMOUNT;
                entity.N_ZBAMOUNT = item.N_ZBAMOUNT;
                entity.N_PLANAMOUNT = item.N_PLANAMOUNT;
                entity.N_SPAMOUNT1 = item.N_SPAMOUNT1;

                entity.N_SPAMOUNT2 = item.N_SPAMOUNT2;
                entity.N_REAMOUNT = item.N_REAMOUNT;

                entity.C_BANK = item.C_BANK;
                entity.C_REMARK = item.C_REMARK;

                entity.N_PROTYPE = item.N_PROTYPE;
                entity.C_PRONAME = item.C_PRONAME;
                if (Insert(entity, base.User.USER_ID.ToString()))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"update qs_planmonth_detail t
   set t.n_amount =(select nvl( sum(t.n_reamount),0)
  from qs_plan_detail3 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_protype = " + N_PROTYPE + @" )
 where t.n_monthapp_id = " + applyId + @"
   and t.n_proid =" + proId;

            if (type == 3)
            {
                //周计划明细
                sql = @"update QS_PLANWEEK_DETAIL t
   set t.n_amount =(select nvl( sum(t.n_reamount),0)
  from qs_plan_detail3 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_protype = " + N_PROTYPE + @" )
 where t.N_WEEKAPP_ID = " + applyId + @"
   and t.n_proid =" + proId;
            }

            if (type == 4)
            {
                //月计划部门详情
                sql = @"update QS_PLANMONTH_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.n_reamount),0)
  from qs_plan_detail3 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_protype = " + N_PROTYPE + @"
   and t.n_userid = " + base.User.USER_ID + @")
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID = " + base.User.USER_ID;
            }

            if (type == 5)
            {
                //周计划部门详情
                sql = @"update QS_PLANWEEK_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.n_reamount),0)
  from qs_plan_detail3 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_protype = " + N_PROTYPE + @"
   and t.n_userid = " + base.User.USER_ID + @")
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID = " + base.User.USER_ID;
            }

            var dt = NoQuery(sql);
        }

        public DataTable ExcelData(int applyId, int proId, int type, int N_PROTYPE)
        {
            string sql = @"select decode(序号, 9999999, null, 序号) 序号,
       单位名称,
       款项类别,
       款项用途,
       挂账金额,
       质保金,
       本月上报资金计划,
       各单位审批金额,
       总部资金审批金额,
       实际金额,
       备注
  from (select t.n_num 序号,
               t.c_customer_name 单位名称,
               t.c_amout_type 款项类别,
               t.c_amout_use 款项用途,
               decode(t.n_gzamount, 0, null, n_gzamount) 挂账金额,
               decode(t.n_zbamount, 0, null, n_zbamount) 质保金,
               decode(t.n_planamount, 0, null, n_planamount) 本月上报资金计划,
               decode(t.n_spamount1, 0, null, n_spamount1) 各单位审批金额,
               decode(t.n_spamount2, 0, null, n_spamount2) 总部资金审批金额,
                t.n_reamount 实际金额,
               t.c_remark 备注
          from qs_plan_detail3 t
         where t.n_protype = " + N_PROTYPE + @"
            and t.N_TYPE=" + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id = " + applyId + @"
        --order by t.n_num, t.n_id3
        union all
        select 9999999,
               '合计',
               '',
               '',
               to_char(sum(t.n_gzamount)),
               to_char(sum(t.n_zbamount)),
               to_char(sum(t.n_planamount)),
               to_char(sum(n_spamount1)),
               to_char(sum(n_spamount2)),
                sum(n_reamount),
               ''
          from qs_plan_detail3 t
         where t.n_protype = " + N_PROTYPE + @"
            and t.N_TYPE=" + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id = " + applyId + @" )
 order by 序号";

            if (N_PROTYPE == 4)
            {
                sql = @"select decode(序号, 9999999, null, 序号) 序号,
       单位名称,
       款项类别, 项目名称,
       款项用途,
       挂账金额,
       质保金,
       本月上报资金计划,
       各单位审批金额,
       总部资金审批金额,
       实际金额,
       备注
  from (select t.n_num 序号,
               t.c_customer_name 单位名称,
               t.c_amout_type 款项类别,C_PRONAME 项目名称,
               t.c_amout_use 款项用途,
               decode(t.n_gzamount, 0, null, n_gzamount) 挂账金额,
               decode(t.n_zbamount, 0, null, n_zbamount) 质保金,
               decode(t.n_planamount, 0, null, n_planamount) 本月上报资金计划,
               decode(t.n_spamount1, 0, null, n_spamount1) 各单位审批金额,
               decode(t.n_spamount2, 0, null, n_spamount2) 总部资金审批金额,
                t.n_reamount 实际金额,
               t.c_remark 备注
          from qs_plan_detail3 t
         where t.n_protype = " + N_PROTYPE + @"
            and t.N_TYPE=" + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id = " + applyId + @"
        --order by t.n_num, t.n_id3
        union all
        select 9999999,
               '合计',
               '','',
               '',
               to_char(sum(t.n_gzamount)),
               to_char(sum(t.n_zbamount)),
               to_char(sum(t.n_planamount)),
               to_char(sum(n_spamount1)),
               to_char(sum(n_spamount2)),
                sum(n_reamount),
               ''
          from qs_plan_detail3 t
         where t.n_protype = " + N_PROTYPE + @"
            and t.N_TYPE=" + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id = " + applyId + @" )
 order by 序号";
            }
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetTable(decimal planappId, decimal type, decimal proType)
        {
            switch (proType)
            {
                case 1:
                    return GetTable1((int)planappId, (int)type);

                case 2:
                    return GetTable2((int)planappId, (int)type);

                case 3:
                    return GetTable3((int)planappId, (int)type);

                case 4:
                    return GetTable4((int)planappId, (int)type);

                default:
                    break;
            }
            var sql1 = string.Format(@"select ROW_NUMBER() OVER(ORDER BY n_id3) as r,n_id3,
       n_pro_id,
       n_type,
       n_planapp_id,
       n_num,
       n_customer_id,
       c_customer_name,
       c_amout_type,
       c_proname,
       c_amout_use,
       n_gzamount,
       n_zbamount,
       n_monthjeuse,
       N_LASTMONTHJEUSE,
       n_planamount,
       n_spamount1,
       n_spamount2,
       n_reamount,
       c_bank,
       n_protype,
       c_remark
  from qs_plan_detail3
 WHERE n_planapp_id = {0}
   and n_type = {1}
   and N_PROTYPE = {2}", planappId, type, proType);

            DataTable dt = new BaseBLL().Query(sql1);
            return dt;
        }

        #region MyRegion

        #region MyRegion1

        public DataTable GetTable1(int applyId, int type)
        {
            switch (type)
            {
                case 1:
                    return GetTableByYear1(applyId);

                case 2:
                    return GetTableByMonth1(applyId);

                case 3:
                    return GetTableByWeek1(applyId);

                default:
                    break;
            }
            return null;
        }

        /// <summary>
        ///安装外包费用(周)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByWeek1(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T1.C_CUSTOMER_NAME) AS R,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.N_USEAMOUNT,
                            T1.C_REMARK,
                            T1.C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_REAMOUNT = 0 THEN '0%'
                                WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_USEAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        FROM QS_PLAN_DETAIL3 T1
                        WHERE
                            T1.N_TYPE = 3 AND
                            T1.N_PROTYPE = 1 AND
                            T1.N_PLANAPP_ID = :APP_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///安装外包费用(月)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByMonth1(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 2 AND
                                T1.N_PROTYPE = 1 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 1 AND
                                T3.N_MONTHAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///安装外包费用(年)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByYear1(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 1 AND
                                T1.N_PROTYPE = 1 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            INNER JOIN QX_PLANYEARAPPLY T4 ON T4.N_YEARAPP_ID = T3.N_YEARAPP_ID AND T4.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 1 AND
                                T4.N_YEARAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        #endregion MyRegion1

        #region MyRegion2

        public DataTable GetTable2(int applyId, int type)
        {
            switch (type)
            {
                case 1:
                    return GetTableByYear2(applyId);

                case 2:
                    return GetTableByMonth2(applyId);

                case 3:
                    return GetTableByWeek2(applyId);

                default:
                    break;
            }
            return null;
        }

        /// <summary>
        ///材料费用(周)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByWeek2(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T1.C_CUSTOMER_NAME) AS R,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.N_USEAMOUNT,
                            T1.C_REMARK,
                            T1.C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_REAMOUNT = 0 THEN '0%'
                                WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_USEAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        FROM QS_PLAN_DETAIL3 T1
                        WHERE
                            T1.N_TYPE = 3 AND
                            T1.N_PROTYPE = 2 AND
                            T1.N_PLANAPP_ID = :APP_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///材料费用(月)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByMonth2(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 2 AND
                                T1.N_PROTYPE = 2 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 2 AND
                                T3.N_MONTHAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///材料费用(年)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByYear2(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 1 AND
                                T1.N_PROTYPE = 2 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            INNER JOIN QX_PLANYEARAPPLY T4 ON T4.N_YEARAPP_ID = T3.N_YEARAPP_ID AND T4.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 2 AND
                                T4.N_YEARAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        #endregion MyRegion2

        #region MyRegion3

        public DataTable GetTable3(int applyId, int type)
        {
            switch (type)
            {
                case 1:
                    return GetTableByYear3(applyId);

                case 2:
                    return GetTableByMonth3(applyId);

                case 3:
                    return GetTableByWeek3(applyId);

                default:
                    break;
            }
            return null;
        }

        /// <summary>
        ///材料费用(周)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByWeek3(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T1.C_CUSTOMER_NAME) AS R,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.N_USEAMOUNT,
                            T1.C_REMARK,
                            T1.C_CUSTOMER_NAME,
                            T1.C_PRONAME,
                            CASE
                                WHEN T1.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_REAMOUNT = 0 THEN '0%'
                                WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_USEAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        FROM QS_PLAN_DETAIL3 T1
                        WHERE
                            T1.N_TYPE = 3 AND
                            T1.N_PROTYPE = 3 AND
                            T1.N_PLANAPP_ID = :APP_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///材料费用(月)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByMonth3(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
                                ELSE T1.C_PRONAME
                            END AS C_PRONAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 2 AND
                                T1.N_PROTYPE = 3 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 3 AND
                                T3.N_MONTHAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.C_PRONAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME AND T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///材料费用(年)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByYear3(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
                                ELSE T1.C_PRONAME
                            END AS C_PRONAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 1 AND
                                T1.N_PROTYPE = 3 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            INNER JOIN QX_PLANYEARAPPLY T4 ON T4.N_YEARAPP_ID = T3.N_YEARAPP_ID AND T4.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 3 AND
                                T4.N_YEARAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME AND T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        #endregion MyRegion3

        #region MyRegion4

        public DataTable GetTable4(int applyId, int type)
        {
            switch (type)
            {
                case 1:
                    return GetTableByYear4(applyId);

                case 2:
                    return GetTableByMonth4(applyId);

                case 3:
                    return GetTableByWeek4(applyId);

                default:
                    break;
            }
            return null;
        }

        /// <summary>
        ///大修费用(周)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByWeek4(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T1.C_CUSTOMER_NAME) AS R,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.N_USEAMOUNT,
                            T1.C_REMARK,
                            T1.C_CUSTOMER_NAME,
                            T1.C_PRONAME,
                            CASE
                                WHEN T1.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_REAMOUNT = 0 THEN '0%'
                                WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T1.N_USEAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        FROM QS_PLAN_DETAIL3 T1
                        WHERE
                            T1.N_TYPE = 3 AND
                            T1.N_PROTYPE = 4 AND
                            T1.N_PLANAPP_ID = :APP_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///大修费用(月)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByMonth4(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
                                ELSE T1.C_PRONAME
                            END AS C_PRONAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 2 AND
                                T1.N_PROTYPE = 4 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 4 AND
                                T3.N_MONTHAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,T1.C_PRONAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME AND T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        /// <summary>
        ///大修费用(年)
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByYear4(int applyId)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T2.C_CUSTOMER_NAME) AS R,
                            CASE
                                WHEN T1.C_CUSTOMER_NAME IS NULL THEN T2.C_CUSTOMER_NAME
                                ELSE T1.C_CUSTOMER_NAME
                            END AS C_CUSTOMER_NAME,
                            CASE
                                WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
                                ELSE T1.C_PRONAME
                            END AS C_PRONAME,
                            T1.N_GZAMOUNT,
                            T1.N_PLANAMOUNT,
                            T1.N_REAMOUNT,
                            T1.C_REMARK,
                            T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                            T2.N_REAMOUNT AS N_WEEK_AMOUNT,
                            CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_USEAMOUNT = 0 THEN '0%'
                                WHEN T2.N_REAMOUNT IS NULL THEN '0%'
                                WHEN T2.N_REAMOUNT = 0 THEN '0%'
                                ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_REAMOUNT*100,6))||'%'
                            END AS N_GOL,T2.N_ID3,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_PROTYPE
                        FROM
                        (
                            SELECT
                                T1.*
                            FROM QS_PLAN_DETAIL3 T1
                            WHERE
                                T1.N_TYPE = 1 AND
                                T1.N_PROTYPE = 4 AND
                                T1.N_PLANAPP_ID = :APP_ID
                        ) T1
                        FULL JOIN
                        (
                            SELECT
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,
                                SUM(
                                    CASE
                                        WHEN T1.N_REAMOUNT IS NULL THEN 0
                                        ELSE T1.N_REAMOUNT
                                    END
                                ) AS N_REAMOUNT,
                                SUM(
                                    CASE
                                        WHEN T1.N_USEAMOUNT IS NULL THEN 0
                                        ELSE T1.N_USEAMOUNT
                                    END
                                ) AS N_USEAMOUNT,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                            FROM QS_PLAN_DETAIL3 T1
                            INNER JOIN QS_PLANWEEKAPPLY T2 ON T1.N_PLANAPP_ID = T2.N_WEEKAPP_ID AND T2.N_BUSINESS_STATE =2
                            INNER JOIN QS_PLANMONTHAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID AND T3.N_BUSINESS_STATE =2
                            INNER JOIN QX_PLANYEARAPPLY T4 ON T4.N_YEARAPP_ID = T3.N_YEARAPP_ID AND T4.N_BUSINESS_STATE =2
                            WHERE
                                T1.N_TYPE = 3 AND
                                T1.N_PROTYPE = 4 AND
                                T4.N_YEARAPP_ID = :APP_ID
                            GROUP BY
                                T1.C_CUSTOMER_NAME,
                                T1.C_PRONAME,T1.N_ID3,T1.N_PRO_ID,T1.N_PLANAPP_ID,T1.N_PROTYPE
                        ) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME AND T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APP_ID", applyId, "int");
            return Query(sql, parms);
        }

        #endregion MyRegion4

        #endregion MyRegion
    }
}